{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.285120Z",
     "end_time": "2024-06-11T20:04:20.754507Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   year market   sale  profit\n0  2010      东  33912    2641\n1  2010      南  32246    2699\n2  2010      西  34792    2574\n3  2010      北  31884    2673\n4  2011      东  31651    2437",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>market</th>\n      <th>sale</th>\n      <th>profit</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2010</td>\n      <td>东</td>\n      <td>33912</td>\n      <td>2641</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2010</td>\n      <td>南</td>\n      <td>32246</td>\n      <td>2699</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010</td>\n      <td>西</td>\n      <td>34792</td>\n      <td>2574</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2010</td>\n      <td>北</td>\n      <td>31884</td>\n      <td>2673</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011</td>\n      <td>东</td>\n      <td>31651</td>\n      <td>2437</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sale = pd.read_csv(r'../data/sale.csv', encoding='gbk')\n",
    "sale.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "   year market   sale  profit\n0  2010      东  33912    2641\n1  2010      南  32246    2699\n2  2010      西  34792    2574\n3  2010      北  31884    2673\n4  2011      东  31651    2437",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>market</th>\n      <th>sale</th>\n      <th>profit</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2010</td>\n      <td>东</td>\n      <td>33912</td>\n      <td>2641</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2010</td>\n      <td>南</td>\n      <td>32246</td>\n      <td>2699</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010</td>\n      <td>西</td>\n      <td>34792</td>\n      <td>2574</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2010</td>\n      <td>北</td>\n      <td>31884</td>\n      <td>2673</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011</td>\n      <td>东</td>\n      <td>31651</td>\n      <td>2437</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "con = sqlite3.connect(':memory:')\n",
    "sale.to_sql('sale', con)\n",
    "newTable = pd.read_sql_query('select year,market,sale,profit from sale', con)\n",
    "newTable.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.757921Z",
     "end_time": "2024-06-11T20:04:20.787605Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "   index  year market   sale  profit\n0      0  2010      东  33912    2641\n1      1  2010      南  32246    2699\n2      2  2010      西  34792    2574\n3      3  2010      北  31884    2673\n4      4  2011      东  31651    2437",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>index</th>\n      <th>year</th>\n      <th>market</th>\n      <th>sale</th>\n      <th>profit</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>2010</td>\n      <td>东</td>\n      <td>33912</td>\n      <td>2641</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>2010</td>\n      <td>南</td>\n      <td>32246</td>\n      <td>2699</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>2010</td>\n      <td>西</td>\n      <td>34792</td>\n      <td>2574</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>2010</td>\n      <td>北</td>\n      <td>31884</td>\n      <td>2673</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>2011</td>\n      <td>东</td>\n      <td>31651</td>\n      <td>2437</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#选择表中所有列\n",
    "sqlResult = pd.read_sql_query('select * from sale', con)\n",
    "sqlResult.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.772536Z",
     "end_time": "2024-06-11T20:04:20.862591Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "   year\n0  2010\n1  2011\n2  2012",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2010</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2012</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#删除重复的行\n",
    "pd.read_sql_query('select DISTINCT year from sale', con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.788102Z",
     "end_time": "2024-06-11T20:04:20.902936Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "   index  year market   sale  profit\n0      8  2012      东  31619    2106\n1     10  2012      西  32103    2593",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>index</th>\n      <th>year</th>\n      <th>market</th>\n      <th>sale</th>\n      <th>profit</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>8</td>\n      <td>2012</td>\n      <td>东</td>\n      <td>31619</td>\n      <td>2106</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10</td>\n      <td>2012</td>\n      <td>西</td>\n      <td>32103</td>\n      <td>2593</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#选择满足条件的行\n",
    "pd.read_sql_query(\"select * from sale where market in ('东','西') and year=2012\", con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.804447Z",
     "end_time": "2024-06-11T20:04:20.941776Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "    year market   sale  profit\n0   2010      西  34792    2574\n1   2011      西  34175    2877\n2   2010      东  33912    2641\n3   2012      南  32443    3124\n4   2010      南  32246    2699\n5   2012      西  32103    2593\n6   2010      北  31884    2673\n7   2012      北  31744    2962\n8   2011      东  31651    2437\n9   2012      东  31619    2106\n10  2011      南  30572    2853\n11  2011      北  30555    2749",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>market</th>\n      <th>sale</th>\n      <th>profit</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2010</td>\n      <td>西</td>\n      <td>34792</td>\n      <td>2574</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011</td>\n      <td>西</td>\n      <td>34175</td>\n      <td>2877</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010</td>\n      <td>东</td>\n      <td>33912</td>\n      <td>2641</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2012</td>\n      <td>南</td>\n      <td>32443</td>\n      <td>3124</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2010</td>\n      <td>南</td>\n      <td>32246</td>\n      <td>2699</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2012</td>\n      <td>西</td>\n      <td>32103</td>\n      <td>2593</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>2010</td>\n      <td>北</td>\n      <td>31884</td>\n      <td>2673</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>2012</td>\n      <td>北</td>\n      <td>31744</td>\n      <td>2962</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2011</td>\n      <td>东</td>\n      <td>31651</td>\n      <td>2437</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>2012</td>\n      <td>东</td>\n      <td>31619</td>\n      <td>2106</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>2011</td>\n      <td>南</td>\n      <td>30572</td>\n      <td>2853</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>2011</td>\n      <td>北</td>\n      <td>30555</td>\n      <td>2749</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#对行进行排序\n",
    "sql = '''select year, market, sale, profit\n",
    "      from sale\n",
    "      order by  sale desc'''\n",
    "pd.read_sql_query(sql, con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.821774Z",
     "end_time": "2024-06-11T20:04:20.957783Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 5.2纵向连接表"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "   0  1  2  3  4  5  6\nx  1  1  1  2  3  4  6\na  a  a  b  c  v  e  g",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>a</td>\n      <td>a</td>\n      <td>b</td>\n      <td>c</td>\n      <td>v</td>\n      <td>e</td>\n      <td>g</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one = pd.read_csv(r'../data/One.csv')\n",
    "one.to_sql('One', con, index=False)\n",
    "one.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.836718Z",
     "end_time": "2024-06-11T20:04:20.975441Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "   0  1  2  3  4\nx  1  2  3  3  5\nb  x  y  z  v  w",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>x</td>\n      <td>y</td>\n      <td>z</td>\n      <td>v</td>\n      <td>w</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "two = pd.read_csv(r'../data/Two.csv')\n",
    "two.to_sql('Two', con, index=False)\n",
    "two.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.850596Z",
     "end_time": "2024-06-11T20:04:20.976955Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "   0  1  2  3  4  5  6  7  8  9\nx  1  1  1  2  2  3  3  4  5  6\na  a  b  x  c  y  v  z  e  w  g",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>4</td>\n      <td>5</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>a</td>\n      <td>b</td>\n      <td>x</td>\n      <td>c</td>\n      <td>y</td>\n      <td>v</td>\n      <td>z</td>\n      <td>e</td>\n      <td>w</td>\n      <td>g</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "union = pd.read_sql('select * from one UNION select * from two', con)\n",
    "union.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.868561Z",
     "end_time": "2024-06-11T20:04:20.976955Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "  0  1  2  3  4  5  6  7  8  9  10 11\nx  1  1  1  2  3  4  6  1  2  3  3  5\na  a  a  b  c  v  e  g  x  y  z  v  w",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n      <th>10</th>\n      <th>11</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>6</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>a</td>\n      <td>a</td>\n      <td>b</td>\n      <td>c</td>\n      <td>v</td>\n      <td>e</td>\n      <td>g</td>\n      <td>x</td>\n      <td>y</td>\n      <td>z</td>\n      <td>v</td>\n      <td>w</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "union_all = pd.read_sql('select * from one UNION ALL select * from two', con)\n",
    "union_all.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.882557Z",
     "end_time": "2024-06-11T20:04:20.977742Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "   0  1  2  3  4\nx  1  1  2  4  6\na  a  b  c  e  g",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n      <td>4</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>a</td>\n      <td>b</td>\n      <td>c</td>\n      <td>e</td>\n      <td>g</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exceptTable = pd.read_sql('select * from one EXCEPT select * from two', con)\n",
    "exceptTable.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.900062Z",
     "end_time": "2024-06-11T20:04:21.001040Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "   0\nx  3\na  v",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>x</th>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>v</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersectTable = pd.read_sql('select * from one INTERSECT select * from two', con)\n",
    "intersectTable.T"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.915725Z",
     "end_time": "2024-06-11T20:04:21.051324Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "    x    a    b\n0   1    a  NaN\n1   1    a  NaN\n2   1    b  NaN\n3   2    c  NaN\n4   3    v  NaN\n5   4    e  NaN\n6   6    g  NaN\n7   1  NaN    x\n8   2  NaN    y\n9   3  NaN    z\n10  3  NaN    v\n11  5  NaN    w",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>x</th>\n      <th>a</th>\n      <th>b</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>a</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1</td>\n      <td>b</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>c</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3</td>\n      <td>v</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>4</td>\n      <td>e</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>6</td>\n      <td>g</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1</td>\n      <td>NaN</td>\n      <td>x</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2</td>\n      <td>NaN</td>\n      <td>y</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>3</td>\n      <td>NaN</td>\n      <td>z</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>3</td>\n      <td>NaN</td>\n      <td>v</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>5</td>\n      <td>NaN</td>\n      <td>w</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([one, two], axis=0, join='outer', ignore_index=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.928863Z",
     "end_time": "2024-06-11T20:04:21.051324Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "   id  a\n0   1  a\n1   2  b\n2   3  c",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>b</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>c</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table1 = pd.read_csv(r'../data/Table1.csv')\n",
    "table1.to_sql('table1', con, index=False)\n",
    "table1.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.947468Z",
     "end_time": "2024-06-11T20:04:21.052331Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "   id  b\n0   4  d\n1   3  e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>b</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>4</td>\n      <td>d</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>3</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table2 = pd.read_csv(r'../data/Table2.csv')\n",
    "table2.to_sql('table2', con, index=False)\n",
    "table2.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.962375Z",
     "end_time": "2024-06-11T20:04:21.124781Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "   id  a  id  b\n0   1  a   4  d\n1   1  a   3  e\n2   2  b   4  d\n3   2  b   3  e\n4   3  c   4  d\n5   3  c   3  e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>a</th>\n      <th>id</th>\n      <th>b</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n      <td>4</td>\n      <td>d</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>a</td>\n      <td>3</td>\n      <td>e</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>b</td>\n      <td>4</td>\n      <td>d</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>b</td>\n      <td>3</td>\n      <td>e</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3</td>\n      <td>c</td>\n      <td>4</td>\n      <td>d</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>3</td>\n      <td>c</td>\n      <td>3</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#笛卡尔积\n",
    "pd.read_sql(\"select * from table1, table2\", con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.976955Z",
     "end_time": "2024-06-11T20:04:21.137965Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "   id  a  id  b\n0   3  c   3  e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>a</th>\n      <th>id</th>\n      <th>b</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>3</td>\n      <td>c</td>\n      <td>3</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#内连接（使用inner join或使用where子句）\n",
    "pd.read_sql(\"select * from table1 as a inner join table2 as b on a.id=b.id\", con)\n",
    "# pd.read_sql(\"select * from table1 as a, table2 as b where a.id=b.id\", con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:20.992475Z",
     "end_time": "2024-06-11T20:04:21.172423Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "   id  a   id     b\n0   1  a  NaN  None\n1   2  b  NaN  None\n2   3  c  3.0     e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>a</th>\n      <th>id</th>\n      <th>b</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>b</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>c</td>\n      <td>3.0</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#左连接\n",
    "pd.read_sql(\"select * from table1 as a left join table2 as b on a.id=b.id\", con)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:21.011005Z",
     "end_time": "2024-06-11T20:04:21.175567Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T20:04:21.023061Z",
     "end_time": "2024-06-11T20:04:21.205670Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
